-----------------------------------------------------------------------
-- Given a document code and RootHierarchyKey returns the HierarchyKey,
-- DocumentVersionKey and DocumentKey of the document if it is found
-- within the given hierarchy. Security IS checked.
-- NOTE: The documentKey returned will be that of the latest version
-----------------------------------------------------------------------
CREATE PROC [dbo].[asi_DocumentKeysByCode]
   @documentCode nvarchar(100),
   @rootHierarchyKey uniqueidentifier,
   @userKey uniqueidentifier,
   @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000' -- if this is empty, we assume the user is not logged in
AS
BEGIN
   SELECT h.HierarchyKey,
          d.DocumentVersionKey,
          d.DocumentKey
     FROM Hierarchy h
          INNER JOIN DocumentMain d ON h.UniformKey = d.DocumentVersionKey AND d.DocumentStatusCode IN (10,20,30,40,60)
    WHERE h.RootHierarchyKey = @rootHierarchyKey
          AND d.DocumentCode = @documentCode
          AND EXISTS(
          SELECT 1
            FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
           WHERE AccessItem.AccessKey = d.AccessKey
             AND UserToken.UserKey = @userKey
             AND (AccessItem.Permission&3)>0)
    ORDER BY d.DocumentStatusCode DESC
END


GO